﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer;

namespace WindowsFormsApplication2
{
    class DatabaseModyfications
    {
        SQLConnection sQLConnection = new SQLConnection();
        public string databaseName = null;
        public string onlineServicesDatabaseName = null;
        List<int> clientsIDList = new List<int>();//TODO: ta lista ma służyc do updatu bazy
        List<Guid> guidsList = new List<Guid>();//TODO: ta lista ma sluzyc do updatu bazy
        List<int> featureGroupUserFeatureGroupIdList = new List<int>();
        List<int> clientsListOfUser = new List<int>();
        List<string> listOfTablesToModify = new List<string>();
        List<string> listOfOSDatabases = new List<string>();
        //todo: czy trzeba updetowac developer authentication - nie zawsze istnieje
        //todo: aktualizacja widoku users
        //todo stwórz uzytkowników
        //System.Data.SqlClient.SqlConnection SqlCon = GetSqlConnection();



        internal List<Guid> GetGUIDSList()
        {
           // List<Guid> guidsList = new List<Guid>();
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();
            System.Data.SqlClient.SqlDataReader SqlDR = GetSqlReader(true, SqlCon, "OSUsersGUID.sql", null);

            while (SqlDR.Read())
            {
                this.guidsList.Add(SqlDR.GetGuid(0));
            }

            SqlCon.Dispose();
            return guidsList;
        }

        System.Data.SqlClient.SqlConnection GetSqlConnection()
        {
            System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(this.serverConnectionString);//zly format
            SqlCon.Open();
            return SqlCon;
        }

        System.Data.SqlClient.SqlDataReader GetSqlReader(bool file, System.Data.SqlClient.SqlConnection SqlCon, string scriptFile, string scriptText)
        {
            SqlCommand guids = new SqlCommand(GetScript(file, scriptFile, scriptText));
            guids.Connection = SqlCon;
            System.Data.SqlClient.SqlDataReader SqlDR;
            SqlDR = guids.ExecuteReader();
            return SqlDR;           
        }

        string GetScript(bool file, string scriptFile, string scriptText)
        {
            string script = null;
            if (file == false)
            {
                script = scriptText;
            }
            else
            {
                FileInfo f = new FileInfo(scriptFile);
                script = f.OpenText().ReadToEnd();
            }
            
            return script;
        }


        internal List<int> GetClientsList()
        {
          //  List<int> clientsIDList = new List<int>();
            string script = "use " + this.databaseName + " select ClientUserClientId from [ClientUser] group by ClientUserClientId";
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();

            System.Data.SqlClient.SqlDataReader SqlDR = GetSqlReader(false, SqlCon, null, script);
            
            while (SqlDR.Read())
            {
                this.clientsIDList.Add(SqlDR.GetInt32(0));
            }

            SqlCon.Dispose();
            return clientsIDList;
        }

        internal List<int> GetFeatureGroupUserIDList()
        {
            //  List<int> clientsIDList = new List<int>();
            string script = "use " + this.databaseName + " select FeatureGroupUserFeatureGroupId from [FeatureGroupUser] group by FeatureGroupUserFeatureGroupId";
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();

            System.Data.SqlClient.SqlDataReader SqlDR = GetSqlReader(false, SqlCon, null, script);

            while (SqlDR.Read())
            {
                this.featureGroupUserFeatureGroupIdList.Add(SqlDR.GetInt32(0));
            }

            SqlCon.Dispose();
            return clientsIDList;
        }

        internal List<string> GetListOfTablesToModify()
        {
            string script = "use " + this.databaseName + " select name from sys.Tables where name like 'ClientUser' or name like 'FeatureGroupUser' or name like 'DeveloperAuthentication'";
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();

            System.Data.SqlClient.SqlDataReader SqlDR = GetSqlReader(false, SqlCon, null, script);

            while (SqlDR.Read())
            {
                this.listOfTablesToModify.Add(SqlDR.GetString(0));
            }

            SqlCon.Dispose();
            return listOfTablesToModify;

        }

        public void InsertIntoClientUser()
        {
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();
            int counter = 0;
            try
            {

                foreach (Guid guidsListItem in guidsList)
                {
                    counter = 0;
                    foreach (int clientsIDListItem in clientsIDList)
                    {
                        string script = null;
                        if (counter == 0)
                        {
                            script = "use " + this.databaseName + " INSERT INTO [ClientUser] VALUES (\'" + guidsListItem + "\'," + clientsIDListItem + ", 1)";
                        }
                        else
                        {
                            script = "use " + this.databaseName + " INSERT INTO [ClientUser] VALUES (\'" + guidsListItem + "\'," + clientsIDListItem + ", 0)";
                        }

                        SqlCommand sqlCom = new SqlCommand(script);
                        sqlCom.Connection = SqlCon;
                        sqlCom.ExecuteNonQuery();
                        counter++;
                    }
                }
            }
            catch
            {
               // throw new Exception("Nie można wykonać skryptu. Dane już istnieją lub próbujesz wprowadzić nie właściwe dane");
                
            }

        }

        public void InsertIntoFeatureGroupUser()
        {

            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();

            try
            {
                foreach (Guid guidsListItem in guidsList)
                {
                    foreach (int featureGroupUserFeatureGroupIdListItem in featureGroupUserFeatureGroupIdList)
                    {
                        string script = "use " + this.databaseName + " INSERT INTO [FeatureGroupUser] VALUES (\'" + guidsListItem + "\'," + featureGroupUserFeatureGroupIdListItem + ")";
                        SqlCommand sqlCom = new SqlCommand(script);
                        sqlCom.Connection = SqlCon;
                        sqlCom.ExecuteNonQuery();
                    }
                }
            }
            catch
            {
               // throw new Exception("Nie można wykonać skryptu. Dane już istnieją lub próbujesz wprowadzić nie właściwe dane");
            }
        }

        public void SetDefaultClientUser(Guid clientUserUserIdentity, int clientUserClientId)
        {
            string script = null;
            SqlCommand sqlCom=null;
            this.GetClientsListOfUser(clientUserUserIdentity);
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();
            this.GetClientsListOfUser(clientUserUserIdentity);
            foreach (int clientsListOfUserItem in clientsListOfUser)
            {
                script = "use " + this.databaseName + " UPDATE [ClientUser] SET [ClientUserIsDefault]= 0 WHERE [ClientUserUserIdentity]= \'" + clientUserUserIdentity+"\'";
                sqlCom = new SqlCommand(script);
                sqlCom.Connection = SqlCon;
                sqlCom.ExecuteNonQuery();
            }

            script = "use " + this.databaseName + " UPDATE [ClientUser] SET [ClientUserIsDefault]= 1 WHERE [ClientUserUserIdentity]= \'" + clientUserUserIdentity + "\' and [ClientUserClientId] =" + clientUserClientId;
            sqlCom = new SqlCommand(script);
            sqlCom.Connection = SqlCon;
            sqlCom.ExecuteNonQuery();         
        }

        internal void GetClientsListOfUser(Guid userGuid)
        {
            //  List<int> clientsIDList = new List<int>();
            clientsListOfUser.Clear();
            string script = "use " + this.databaseName + " select ClientUserClientId from [ClientUser] where ClientUserUserIdentity like \'" + userGuid + "\'";
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();

            System.Data.SqlClient.SqlDataReader SqlDR = GetSqlReader(false, SqlCon, null, script);

            while (SqlDR.Read())
            {
                this.clientsListOfUser.Add(SqlDR.GetInt32(0));
            }

            SqlCon.Dispose();
            //this.clientsListOfUser clientsListOfUser;
        }



        internal void ChangeUserView()
        {
            listOfOSDatabases.Clear();
            string script = "USE " + this.databaseName + " GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [dbo].[User] AS SELECT UserUniqueIdentity AS UserIdentity, UserEmail, UserFirstName, UserLastName, UserName, UserIsActive FROM " + this.onlineServicesDatabaseName + ".dbo.[User] GO";
            SqlCommand sqlCom = new SqlCommand(script);
            System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();
            sqlCom.Connection = SqlCon;
            sqlCom.ExecuteNonQuery();

            SqlCon.Dispose();
        }

        //bool HasDefaultClientUser(Guid ClientUserUserIdentity)
        //{
        //    System.Data.SqlClient.SqlConnection SqlCon = this.GetSqlConnection();
        //    string script = "use " + this.databaseName + " select ClientUserIsDefault from ClientUser where ClientUserIsDefault like 1 and  ClientUserUserIdentity like \'" + ClientUserUserIdentity + "\'";
        //    SqlCommand sqlCom = new SqlCommand(script);
        //    sqlCom.Connection = SqlCon;
            

        //    System.Data.SqlClient.SqlDataReader SqlDR;
        //    SqlDR = sqlCom.ExecuteReader();
        //    bool hasDefaultClientUser= new bool();
        //    if (SqlDR == null)
        //    {
        //        hasDefaultClientUser = false;
        //    }
        //    else
        //    {
        //        hasDefaultClientUser = true;
        //    }
        //    return hasDefaultClientUser; 
        //}

        public string serverConnectionString { get; set; }
    }
}
